from sqlalchemy import create_engine,MetaData
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import sessionmaker

engine = create_engine("mysql+pymysql://root:9999@localhost/zxzl",encoding='utf8')
metadata = MetaData()
metadata.reflect(engine, only=['user', 'role', 'user_link_role'])
Base = automap_base(metadata=metadata)   # 表单自动映射
session = sessionmaker(bind=engine)     # 订制会话
Base.prepare()



# 判断一个人是否对应职位或公司
# user_id, role_id
# user_id, company_id
def judge_a_staff(user_id, role_id=None, company_id=None):
        his_info = search_user_link_role(user_id=user_id)
        if not his_info:
                # print('user_id不存在！')
                return False

        rt = []
        if role_id and company_id:
                for i in his_info:
                        if i['role_id'] == role_id and i['company_id'] == company_id:
                                rt.append(i)
        elif role_id:
                for i in his_info:
                        if i['role_id'] == role_id:
                                rt.append(i)
        elif company_id:
                for i in his_info:
                        if i['company_id'] == company_id:
                                rt.append(i)
        else:
                return False
        return rt


def insert_user_link_role(user_id, role_id, company_id=None):
        U_l_r = Base.classes.user_link_role
        sess = session()

        if user_id and role_id:         # 输入合理
                exist = search_user_link_role(company_id=company_id, role_id=role_id,
                                             user_id=user_id)
                if not exist:   # 数据唯一性插入
                        u_l_r = U_l_r(user_id=user_id,
                                          role_id=role_id,
                                          company_name=company_id)
                        try:
                                sess.add(u_l_r)
                                sess.commit()
                                sess.close()
                                print('插入成功！')
                        except Exception as e:
                                print(e)
                                print('插入错误：外键越界！')
                else:
                        print('插入错误：数据已存在！')
        else:
                print('插入错误：输入为空！')


# ①：输入company_id和role_id和user_id
# ②：输入company_id和role_id
# ③：输入user_id
# ④：输入company_id
def search_user_link_role(user_id=None, company_id=None, role_id=None):
        U_l_r = Base.classes.user_link_role
        sess = session()

        query = sess.query(U_l_r)
        if user_id and company_id and role_id:
                u_l_r = query.filter_by(user_id=user_id, role_id=role_id, company_name=company_id).all()
        elif company_id and role_id:
                u_l_r = query.filter_by(company_name=company_id, role_id=role_id).all()
        elif user_id:
                u_l_r = query.filter_by(user_id=user_id).all()
        elif company_id:
                u_l_r = query.filter_by(company_id=company_id).all()
        else:
                print('u_l_r查询错误：参数错误！')
                return False

        u_l_r_dump = [{'user_id': u_l_r[i].user_id, 'role_id': u_l_r[i].role_id, 'company_id': u_l_r[i].company_name}
                      for i in range(len(u_l_r))]
        try:
                sess.commit()
                sess.close()
                # print('查询结果:', u_l_r_dump)
                if u_l_r:
                        return u_l_r_dump
                else:
                        return False
        except Exception as e:
                print(e)
                print('查询错误：数据库错误！')
                return False


# ①：输入pri_key
# ②：输入company, user_id, role_id
def delete_user_link_role(pri_key=None, company_id=None, user_id=None, role_id=None):
        U_l_r = Base.classes.user_link_role
        sess = session()

        if pri_key:     # 根据主键删除
                u_l_r = sess.query(U_l_r).filter_by(id=pri_key).all()
                print('pri_key:', pri_key)
        else:   # 根据公司id，用户id，角色id删除
                u_l_r = sess.query(U_l_r).filter_by(company_name=company_id, user_id=user_id,
                                                    role_id=role_id).all()
                print('user_id:', user_id, 'role_id:', role_id, 'company_id:', company_id)
        try:
                for i in u_l_r:
                        sess.delete(i)
                sess.commit()
                sess.close()
                print('删除成功！')
                return True
        except Exception as e:
                print(e)
                sess.rollback()
                return False


if __name__ == '__main__':
        # insert_user_link_role(6, 6, 2)
        # select_user_link_role(1, 3)
        # delete_user_link_role(user_id=1, role_id=6, company_id=1)
        search_user_link_role(user_id=1)

